<?php
/**
 * Created by PhpStorm.
 * User: 戎大富
 * Date: 2017/3/28
 * Time: 11:05
 */
namespace app\common\model;
use think\Db;
class AdoptSq extends \think\Model{
    protected $table='up_dog_proposer';
    public static $proposer_long_time = array(//已在本市居住时间
        '1' => '0-12个月',
        '2' => '1-3年',
        '3' => '4-6年',
        '4' => '7年以上',
    );
    public static $proposer_experience = array(//养狗经验
        '1' => '无',
        '2' => '少于1年',
        '3' => '1-3年',
        '4' => '3年以上',
    );
    public static $proposer_petyet = array(//领养人是否已有宠物
        '1' => '有',
        '2' => '没有',
    );
    public function relData($pagesize,$search){
        $model = self::instance();
        $where = '';
        if($search[0]!=''){
            $where['u.proposer_long_time'] = ['=',$search[0]];
        }
        if($search[1]!=''){
            $where['u.proposer_experience'] = ['=',$search[1]];
        }
        if($search[2]!=''){
            $where['u.proposer_petyet'] = ['=',$search[2]];
        }
        if($search[3]!=''){
            $where['uuu.user_username|uuu.user_phone']=['like',"%$search[3]%"];
        }
        if($search[4]!=''){
            $where['u.proposer_abode_province'] =['=',$search[4]];
        }

        if($search[5]!=''){
            $where['u.proposer_abode_city'] =['=',$search[5]];
        }
        if($search[6]!=''){
            $where['u.proposer_abode_district'] =['=',$search[6]];
        }
        if($search[7] > 0 && $search[8] > 0){
            $where['u.proposer_datetime'] = array('between',array($search[7],$search[8]));
        }else if($search[7] > 0){
            $where['u.proposer_datetime'] = array('gt',$search[7]);
        }else if($search[8]> 0){
            $where['u.proposer_datetime'] = array('lt',$search[8]);
        }
        $where['u.proposer_user_id'] = array('gt', 0);
        $list = $model->alias('u')
            ->join('up_u_user uuu','uuu.user_id  = u.proposer_user_id','left')
            ->join('up_area ua1','ua1.district_id = u.proposer_abode_province','left')
            ->join('up_area ua2','ua2.district_id = u.proposer_abode_city','left')
            ->join('up_area ua3','ua3.district_id = u.proposer_abode_district','left')
            ->join('up_dog ud', 'ud.dog_id = u.dog_id')
            ->where($where)
            ->order('u.proposer_id desc')
            ->field('u.*,ud.udf_id,uuu.user_username,uuu.user_id,ua1.district as province,ua2.district as city,ua3.district as district')
            ->paginate($pagesize);
        foreach ($list as $key=>&$vo){
            $whereMap['cohabit_id'] = array('IN',$vo['proposer_cohabit']);
            $cohabit = Db::table('up_proposer_cohabit')->where($whereMap)->column('cohabit_name');
            if(count($cohabit)>0){//同住人员
                if($vo['proposer_cohabit_other']!=''){
                    $vo['cohabit'] = implode(',',$cohabit);
                    $vo['cohabit'] = $vo['cohabit'].','.$vo['proposer_cohabit_other'];
                }else{
                    $vo['cohabit'] = implode(',',$cohabit);
                }
            }else{
                $vo['cohabit'] = $vo['proposer_cohabit_other'];
            }

            if($vo['proposer_environment']!==0){//居住环境
                $whereE['environment_id'] = array('IN',$vo['proposer_environment']);
                $environment_name = Db::table('up_proposer_environment')->where($whereE)->value('environment_name');
                $vo['proposer_environment_other'] = $environment_name;
              }


            if($vo['proposer_house']!==0){//领养人居住房子属于
                $whereH['house_id'] = array('IN',$vo['proposer_house']);
                $house_name = Db::table('up_proposer_house')->where($whereH)->value('house_name');
                $vo['proposer_house_other'] = $house_name;
            }

            $whereR['reason_id'] = array('IN',$vo['proposer_reason']);
            $reason_name = Db::table('up_proposer_reason')->where($whereR)->column('reason_name');
            if(count($reason_name)>0){//领养原因
                if($vo['proposer_reason_other']!=''){
                    $vo['reason_name'] = implode(',',$reason_name);
                    $vo['reason_name'] = $vo['reason_name'].','.$vo['proposer_reason_other'];
                }else{
                    $vo['reason_name'] = implode(',',$reason_name);
                }
            }else{
                $vo['reason_name'] = $vo['proposer_reason_other'];
            }

            $whereC['care_id'] = array('IN',$vo['proposer_care']);
            $care_name = Db::table('up_proposer_care')->where($whereC)->column('care_name');
            if(count($care_name)>0){//领养后照顾人
                if($vo['proposer_care_other']!=''){
                    $vo['care_name'] = implode(',',$care_name);
                    $vo['care_name'] = $vo['care_name'].','.$vo['proposer_care_other'];
                }else{
                    $vo['care_name'] = implode(',',$cohabit);
                }
            }else{
                $vo['care_name'] = $vo['proposer_care_other'];
            }
        }
        return $list;
    }

    /**
     * @param $search
     * @return false|\PDOStatement|string|\think\Collection
     * excel导出
     */
    public function excelData($search){
        $model = self::instance();
        $where = '';
        if($search[0]!=''){
            $where['u.proposer_long_time'] = ['=',$search[0]];
        }
        if($search[1]!=''){
            $where['u.proposer_experience'] = ['=',$search[1]];
        }
        if($search[2]!=''){
            $where['u.proposer_petyet'] = ['=',$search[2]];
        }
        if($search[3]!=''){
            $where['uuu.user_username|uuu.user_phone']=['like',"%$search[3]%"];
        }
        if($search[4]!=''){
            $where['u.proposer_abode_province'] =['=',$search[4]];
        }

        if($search[5]!=''){
            $where['u.proposer_abode_city'] =['=',$search[5]];
        }
        if($search[6]!=''){
            $where['u.proposer_abode_district'] =['=',$search[6]];
        }
        if($search[7] > 0 && $search[8] > 0){
            $where['u.proposer_datetime'] = array('between',array($search[7],$search[8]));
        }else if($search[7] > 0){
            $where['u.proposer_datetime'] = array('gt',$search[7]);
        }else if($search[8]> 0){
            $where['u.proposer_datetime'] = array('lt',$search[8]);
        }
        $where['u.proposer_user_id'] = array('gt', 0);
        $list = $model->alias('u')
            ->join('up_u_user uuu','uuu.user_id  = u.proposer_user_id','left')
            ->join('up_area ua1','ua1.district_id = u.proposer_abode_province','left')
            ->join('up_area ua2','ua2.district_id = u.proposer_abode_city','left')
            ->join('up_area ua3','ua3.district_id = u.proposer_abode_district','left')
            ->join('up_dog ud','ud.dog_id=u.dog_id','left')
            ->join('up_dog_family udf','udf.udf_id=ud.udf_id','left')
            ->where($where)
            ->order('u.proposer_id desc')
            ->field('udf.udf_condition,udf.udf_sex,udf.udf_size,udf.udf_dog_vaccine,
                    udf.udf_dog_sterilization,udf.udf_dog_disability,udf.udf_age,udf.udf_id,
                    u.*,uuu.user_username,uuu.user_gender,uuu.user_card,uuu.equipment,
                    ud.user_id as ud_d_userId,ud.dog_adopt_status
                ,ua1.district as province,ua2.district as city,ua3.district as district')
            ->select();
        foreach ($list as $key=>&$vo){
            $whereMap['cohabit_id'] = array('IN',$vo['proposer_cohabit']);
            $cohabit = Db::table('up_proposer_cohabit')->where($whereMap)->column('cohabit_name');
            if(count($cohabit)>0){//同住人员
                if($vo['proposer_cohabit_other']!=''){
                    $vo['cohabit'] = implode(',',$cohabit);
                    $vo['cohabit'] = $vo['cohabit'].','.$vo['proposer_cohabit_other'];
                }else{
                    $vo['cohabit'] = implode(',',$cohabit);
                }
            }else{
                $vo['cohabit'] = $vo['proposer_cohabit_other'];
            }

            if($vo['proposer_environment']!==0){//居住环境
                $whereE['environment_id'] = array('IN',$vo['proposer_environment']);
                $environment_name = Db::table('up_proposer_environment')->where($whereE)->value('environment_name');
                $vo['proposer_environment_other'] = $environment_name;
            }


            if($vo['proposer_house']!==0){//领养人居住房子属于
                $whereH['house_id'] = array('IN',$vo['proposer_house']);
                $house_name = Db::table('up_proposer_house')->where($whereH)->value('house_name');
                $vo['proposer_house_other'] = $house_name;
            }

            $whereR['reason_id'] = array('IN',$vo['proposer_reason']);
            $reason_name = Db::table('up_proposer_reason')->where($whereR)->column('reason_name');
            if(count($reason_name)>0){//领养原因
                if($vo['proposer_reason_other']!=''){
                    $vo['reason_name'] = implode(',',$reason_name);
                    $vo['reason_name'] = $vo['reason_name'].','.$vo['proposer_reason_other'];
                }else{
                    $vo['reason_name'] = implode(',',$reason_name);
                }
            }else{
                $vo['reason_name'] = $vo['proposer_reason_other'];
            }

            $whereC['care_id'] = array('IN',$vo['proposer_care']);
            $care_name = Db::table('up_proposer_care')->where($whereC)->column('care_name');
            if(count($care_name)>0){//领养后照顾人
                if($vo['proposer_care_other']!=''){
                    $vo['care_name'] = implode(',',$care_name);
                    $vo['care_name'] = $vo['care_name'].','.$vo['proposer_care_other'];
                }else{
                    $vo['care_name'] = implode(',',$cohabit);
                }
            }else{
                $vo['care_name'] = $vo['proposer_care_other'];
            }
        }
        return $list;
    }

    /**
     * @param $search
     * @return false|\PDOStatement|string|\think\Collection
     * 柱状图统计
     */
    public function branch($search){
        $model = self::instance();
        $where = '';
        if($search[0]!=''){
            $where['u.proposer_abode_province'] = array('=',$search[0]);
        }
        if($search[1]!=''){
            $where['u.proposer_abode_city'] = array('=',$search[1]);
        }
        if($search[2]!=''){
            $where['u.proposer_abode_district'] = array('=',$search[2]);
        }
        if($search[3]==''&&$search[4]==''){
            $time = date('Y-m-d');
            $start = date('Y-m-d',strtotime("-15 day"));
            $where['u.proposer_date'] = array('between',array($start,$time));
        } elseif($search[3] > 0 && $search[4] > 0){
            $where['u.proposer_date'] = array('between',array($search[3],$search[4]));
        }else if($search[3] > 0){
            $where['u.proposer_date'] = array('gt',$search[3]);
        }else if($search[4]> 0){
            $where['u.proposer_date'] = array('lt',$search[4]);
        }else{
            $where['u.proposer_date'] = array('gt',0);
        }
        $branch = $model->alias('u')
            ->group('u.proposer_date')
            ->join('up_area ua1','ua1.district_id = u.proposer_abode_province','left')
            ->join('up_area ua2','ua2.district_id = u.proposer_abode_city','left')
            ->join('up_area ua3','ua3.district_id = u.proposer_abode_district','left')
            ->where($where)
            ->field('count(proposer_id) as value,u.proposer_date')->select();
        //总的数据
        return $branch;
    }

    /**
     * @param $search
     * @return false|\PDOStatement|string|\think\Collection
     * 统计
     */
    public function totalNum($search,$status=''){
        $model = self::instance();
        $where = '';
        if($search[0]!=''){
            $where['u.proposer_abode_province'] = array('=',$search[0]);
        }
        if($search[1]!=''){
            $where['u.proposer_abode_city'] = array('=',$search[1]);
        }
        if($search[2]!=''){
            $where['u.proposer_abode_district'] = array('=',$search[2]);
        }
        if($search[3]==''&&$search[4]==''){
            $time = date('Y-m-d');
            $start = date('Y-m-d',strtotime("-15 day"));
            $where['u.proposer_date'] = array('between',array($start,$time));
        } elseif($search[3] > 0 && $search[4] > 0){
            $where['u.proposer_date'] = array('between',array($search[3],$search[4]));
        }else if($search[3] > 0){
            $where['u.proposer_date'] = array('egt',$search[3]);
        }else if($search[4]> 0){
            $where['u.proposer_date'] = array('elt',$search[4]);
        }else{
            $where['u.proposer_date'] = array('egt',0);
        }
        if($status!=''){
            $where['u.proposer_status'] = $status;
        }
        $branch = $model->alias('u')
            ->group('u.dog_id')
            ->join('up_area ua1','ua1.district_id = u.proposer_abode_province','left')
            ->join('up_area ua2','ua2.district_id = u.proposer_abode_city','left')
            ->join('up_area ua3','ua3.district_id = u.proposer_abode_district','left')
            ->where($where)
            ->count();
        //总的数据
        return $branch;
    }

    /**
     * @param $search
     * @return int|string
     * 统计用户年龄
     */
    public function userAge($search,$field){
        $result = array();
        $model = self::instance();
        $where = '';
        if($search[0]!=''){
            $where['u.proposer_abode_province'] = array('=',$search[0]);
        }
        if($search[1]!=''){
            $where['u.proposer_abode_city'] = array('=',$search[1]);
        }
        if($search[2]!=''){
            $where['u.proposer_abode_district'] = array('=',$search[2]);
        }
        if($search[3]==''&&$search[4]==''){
            $time = date('Y-m-d');
            $start = date('Y-m-d',strtotime("-1 month"));
            $where['u.proposer_date'] = array('between',array($start,$time));
        } else if($search[3] > 0 && $search[4] > 0){
            $where['u.proposer_date'] = array('between',array($search[3],$search[4]));
        }else if($search[3] > 0){
            $where['u.proposer_date'] = array('egt',$search[3]);
        }else if($search[4]> 0){
            $where['u.proposer_date'] = array('elt',$search[4]);
        }else{
            $where['u.proposer_date'] = array('egt',0);
        }

        $branch = $model->alias('u')
            ->join('up_area ua1','ua1.district_id = u.proposer_abode_province','left')
            ->join('up_area ua2','ua2.district_id = u.proposer_abode_city','left')
            ->join('up_area ua3','ua3.district_id = u.proposer_abode_district','left')
            ->join('up_u_user uuu','uuu.user_id = u.proposer_user_id','left')
            ->where($where)
            ->column('uuu.'.$field);
            foreach ($branch as $key=>$vo){
                $result[]=$this->getAgeByID($vo);
            }
            $a = array_unique($result);//去重
            sort($a);//排序
            return $a;
    }

    /**
     * @param $search
     * @return int|string
     * 统计已在本市和养狗经验，家中是否有宠物
     */
    public function totalData($search,$field){
        $model = self::instance();
        $where = '';

        if($search[0]!=''){
            $where['u.proposer_abode_province'] = array('=',$search[0]);
        }
        if($search[1]!=''){
            $where['u.proposer_abode_city'] = array('=',$search[1]);
        }
        if($search[2]!=''){
            $where['u.proposer_abode_district'] = array('=',$search[2]);
        }
        if($search[3]==''&&$search[4]==''){
            $time = date('Y-m-d');
            $start = date('Y-m-d',strtotime("-1 month"));
            $where['u.proposer_date'] = array('between',array($start,$time));
        } else if($search[3] > 0 && $search[4] > 0){
            $where['u.proposer_date'] = array('between',array($search[3],$search[4]));
        }else if($search[3] > 0){
            $where['u.proposer_date'] = array('egt',$search[3]);
        }else if($search[4]> 0){
            $where['u.proposer_date'] = array('elt',$search[4]);
        }else{
            $where['u.proposer_date'] = array('gt',0);
        }

        $branch = $model->alias('u')
            ->group('u.'.$field)
            ->join('up_area ua1','ua1.district_id = u.proposer_abode_province','left')
            ->join('up_area ua2','ua2.district_id = u.proposer_abode_city','left')
            ->join('up_area ua3','ua3.district_id = u.proposer_abode_district','left')
            ->join('up_u_user uuu','uuu.user_id = u.proposer_user_id','left')
            ->where($where)
            ->field('count(u.proposer_id) as value,u.'.$field)->select();
        return $branch;
    }


    /**
     * @param $search
     * @return int|string
     * 统计已在本市和养狗经验，家中是否有宠物总数
     */
    public function total($search){
        $model = self::instance();
        $where = '';
        if($search[0]!=''){
            $where['u.proposer_abode_province'] = array('=',$search[0]);
        }
        if($search[1]!=''){
            $where['u.proposer_abode_city'] = array('=',$search[1]);
        }
        if($search[2]!=''){
            $where['u.proposer_abode_district'] = array('=',$search[2]);
        }
        if($search[3]==''&&$search[4]==''){
            $time = date('Y-m-d');
            $start = date('Y-m-d',strtotime("-1 month"));
            $where['u.proposer_date'] = array('between',array($start,$time));
        } else if($search[3] > 0 && $search[4] > 0){
            $where['u.proposer_date'] = array('between',array($search[3],$search[4]));
        }else if($search[3] > 0){
            $where['u.proposer_date'] = array('egt',$search[3]);
        }else if($search[4]> 0){
            $where['u.proposer_date'] = array('elt',$search[4]);
        }else{
            $where['u.proposer_date'] = array('gt',0);
        }

        $branch = $model->alias('u')
            ->join('up_area ua1','ua1.district_id = u.proposer_abode_province','left')
            ->join('up_area ua2','ua2.district_id = u.proposer_abode_city','left')
            ->join('up_area ua3','ua3.district_id = u.proposer_abode_district','left')
            ->join('up_u_user uuu','uuu.user_id = u.proposer_user_id','left')
            ->where($where)
            ->count();
        return $branch;
    }
    /**
     * @param $map
     * @param $search
     * @return int|string
     * 统计居住环境，房屋
     */
    public function forTotal($map,$search){
        $model = self::instance();
        $where = '';
        if($search[0]!=''){
            $where['u.proposer_abode_province'] = array('=',$search[0]);
        }
        if($search[1]!=''){
            $where['u.proposer_abode_city'] = array('=',$search[1]);
        }
        if($search[2]!=''){
            $where['u.proposer_abode_district'] = array('=',$search[2]);
        }
        if($search[3]==''&&$search[4]==''){
            $time = date('Y-m-d');
            $start = date('Y-m-d',strtotime("-1 month"));
            $where['u.proposer_date'] = array('between',array($start,$time));
        } else if($search[3] > 0 && $search[4] > 0){
            $where['u.proposer_date'] = array('between',array($search[3],$search[4]));
        }else if($search[3] > 0){
            $where['u.proposer_date'] = array('egt',$search[3]);
        }else if($search[4]> 0){
            $where['u.proposer_date'] = array('elt',$search[4]);
        }else{
            $where['u.proposer_date'] = array('gt',0);
        }

        $branch = $model->alias('u')
            ->join('up_area ua1','ua1.district_id = u.proposer_abode_province','left')
            ->join('up_area ua2','ua2.district_id = u.proposer_abode_city','left')
            ->join('up_area ua3','ua3.district_id = u.proposer_abode_district','left')
            ->join('up_u_user uuu','uuu.user_id = u.proposer_user_id','left')
            ->where($where)
            ->where($map)
            ->count();
        return $branch;
    }
    /**
     * @param $search
     * @return int|string
     * 统计用户性别
     */
    public function userSex($search){
        $model = self::instance();
        $where = '';
        if($search[0]!=''){
            $where['u.proposer_abode_province'] = array('=',$search[0]);
        }
        if($search[1]!=''){
            $where['u.proposer_abode_city'] = array('=',$search[1]);
        }
        if($search[2]!=''){
            $where['u.proposer_abode_district'] = array('=',$search[2]);
        }
//        if($search[3] > 0 && $search[4] > 0){
//
//            $where['u.proposer_date'] = array('between',array($search[3],$search[4]));
//        }else if($search[3] > 0){
//            $where['u.proposer_date'] = array('gt',$search[3]);
//        }else if($search[4]> 0){
//            $where['u.proposer_date'] = array('lt',$search[4]);
//        }else{
//            $where['u.proposer_date'] = array('gt',0);
//        }
        if($search[3]==''&&$search[4]==''){
            $time = date('Y-m-d');
            $start = date('Y-m-d',strtotime("-1 month"));
            $where['u.proposer_date'] = array('between',array($start,$time));
        } else if($search[3] > 0 && $search[4] > 0){
            $where['u.proposer_date'] = array('between',array($search[3],$search[4]));
        }else if($search[3] > 0){
            $where['u.proposer_date'] = array('egt',$search[3]);
        }else if($search[4]> 0){
            $where['u.proposer_date'] = array('elt',$search[4]);
        }else{
            $where['u.proposer_date'] = array('gt',0);
        }
        $branch = $model->alias('u')
            ->group('uuu.user_gender')
            ->join('up_area ua1','ua1.district_id = u.proposer_abode_province','left')
            ->join('up_area ua2','ua2.district_id = u.proposer_abode_city','left')
            ->join('up_area ua3','ua3.district_id = u.proposer_abode_district','left')
            ->join('up_u_user uuu','uuu.user_id = u.proposer_user_id','left')
            ->where($where)
            ->field('uuu.user_gender,count(uuu.user_id) as value')
            ->select();
        $count = $model->alias('u')
            ->join('up_area ua1','ua1.district_id = u.proposer_abode_province','left')
            ->join('up_area ua2','ua2.district_id = u.proposer_abode_city','left')
            ->join('up_area ua3','ua3.district_id = u.proposer_abode_district','left')
            ->join('up_u_user uuu','uuu.user_id = u.proposer_user_id','left')
            ->where($where)
            ->count();
        $result['branch'] =$branch;
        $result['count'] =$count;
        return $result;
    }


    public function getAgeByID($id){
        //过了这年的生日才算多了1周岁
        if(empty($id)) return '';
//        $date=strtotime(substr($id,6,8));
        $date=substr($id,6,4);
        //获得出生年月日的时间戳
//        $today=strtotime('today');
        $today=date('Y');
        //获得今日的时间戳
//        $diff=floor(($today-$date)/86400/365);
        $diff=floor($today-$date);
        //得到两个日期相差的大体年数
        return $diff;
        //strtotime加上这个年数后得到那日的时间戳后与今日的时间戳相比
//        $age=strtotime(substr($id,6,8).' +'.$diff.'years')>$today?($diff+1):$diff;
//        return $age;
    }

    private static $instance;

    public static function instance()
    {
        if (!(self::$instance instanceof self)) {
            self::$instance = new self();
        }
        return self::$instance;
    }
}